Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MOVINGAVERAGE

The MOVINGAVERAGE function (abbreviated MVAVG) computes a series of averages for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGAVERAGE computes the average of the data in the range specified, relative to the current dimension value.

When the data being averaged has only one dimension, MOVINGAVERAGE produces a single series of averages, one for each dimension value in status. When the data has dimensions other than the one being averaged over, MOVINGAVERAGE produces a separate series of averages for each combination of values in the status list of the other dimensions.

Return Value

DECIMAL

Syntax

MOVINGAVERAGE(expression, start, stop, step, -

[dimension [STATUS|limit-clause]])

Arguments

expression

  A numeric variable or calculation whose values you want to average; for example, `units` or `sales-expense`.

start

  A whole number that specifies the starting point of the range over which you want to average. The range is specified relative to the current value of dimension. Zero (`0`) refers to the current value, and `-1` refers to the value preceding the current value. A comma is required before a negative start number.

  Each average is based on data for a specified range of dimension values preceding, including, or following the one for which the average is being calculated. To count the values in the range, MOVINGAVERAGE uses the default status, unless you use the STATUS keyword or the limit-clause argument to specify a different dimension status.

stop

  A whole number that specifies the ending point of the range over which you want to average. A negative stop number must be preceded by a comma.

step

  A positive whole number that specifies whether to average over every value in the range, every other value, every third value, and so on. A value of `1` for step means average over every value. A value of `2` means average over the first value, the third value, the fifth value, and so on. For example, when the current month is `Jun96` and the start and stop values are `-3` and `3`, a step value of `2` means average over `Mar96`, `May96`, `Jul96`, and `Sep96`.

dimension

  The dimension over which the moving average is calculated. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR.

  When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGAVERAGE to use that dimension, you can omit the dimension argument.

STATUS

  Specifies that MOVINGAVERAGE should use the current status list (that is, only the dimension values currently in status in their current status order) when calculating the moving average.

limit-clause

  Specifies that MOVINGAVERAGE should use the default status limited by limit-clause when calculating the moving average. You can use any valid LIMIT clause (see the entry for the LIMIT command for further information). To specify that MOVINGAVERAGE should use the current status limited by limit-clause when calculating the moving average, specify a LIMIT function for limit-clause.

Examples

Example 18-9 Calculating a Moving Average

Suppose you have a variable called `sales` that is dimensioned by a hierarchical dimension named `time`, a dimension named `product`, a dimension named `timelevelnames` that contains the names of the levels of `time` (for example, `Quarter` and `Year`), and a relation named `time.levelrels` that relates the values of `time` to the values of `timelevelnames`. Assume also that using the following statements you limit `product` to `Womens - Trousers` and time to quarters from `Q4-1999` to present.

    LIMIT product TO 'Womens - Trousers'
    LIMIT timelevelnames TO 'Quarter'
    LIMIT time TO time.levelrels
    LIMIT time REMOVE 'Q1-1999' 'Q2-1999' 'Q3-1999'

After you have limited `product` and `sales`, you issue the following report statement.

    REPORT DOWN time sales -
    HEADING 'Running Yearly\nTotal' MOVINGTOTAL(sales, -4, 0, 1, time, -
         LEVELREL time.levelrels) -
    HEADING 'Minimum\nQuarter' MOVINGMIN(sales, -4, 0, 1, time, -
         LEVELREL time.levelrels) -
    HEADING 'Maximum\nQuarter' MOVINGMAX(sales, -4, 0, 1, time, -
         LEVELREL time.levelrels) -
    HEADING 'Average\nQuarter' MOVINGAVERAGE(sales, -4, 0, 1, time, -
         LEVELREL time.levelrels)

The following report was created by the preceding statement.

    -----------------------PRODUCT------------------------
                   ------------------Womens - Trousers-------------------
                              Running
                              Yearly     Minimum    Maximum    Average
    TIME           SALES      Total      Quarter    Quarter    Quarter
    -------------- ---------- ---------- ---------- ---------- ----------
    Q4-1999         416            1,386        233        480     346.50
    Q1-2000         465            1,851        233        480     370.20
    Q2-2000         351            1,969        257        480     393.80
    Q3-2000         403            2,115        351        480     423.00
    Q4-2000         281            1,916        281        465     383.20
    Q1-2001         419            1,919        281        465     383.80
    Q2-2001         349            1,803        281        419     360.60
    Q3-2001         467            1,919        281        467     383.80
    Q4-2001         484            2,000        281        484     400.00
    Q1-2002         362            2,081        349        484     416.20
    Q2-2002         237            1,899        237        484     379.80
    Q3-2002         497            2,047        237        497     409.40
    Q4-2002         390            1,970        237        497     394.00